var createError = require('http-errors');
var express = require('express');
var path = require('path');
var cookieParser = require('cookie-parser');
var logger = require('morgan');

var mysql=require('mysql');
var captcha=require('svg-captcha')
var session=require('express-session')
var multer=require('multer')
var bodyparser=require("body-parser")
var crypto=require("crypto")
// var md5=crypto.createHash('md5')
var indexRouter = require('./routes/index');
var usersRouter = require('./routes/users');
var app = express();



var db=mysql.createConnection({
  host:'localhost',
  user:'root',
  password:'root',
  database:'tianmao'
})
db.connect((error)=>{
  if(error!=null){
    console.log('链接失败')
    return false
  }else{
    console.log('链接成功')
  }
})

var storage=multer.diskStorage({
  destination:(req,file,cd)=>{
    cd(null,"./public/images/shangpin")
  },
  filename:(req,file,cd)=>{
    let ext='.'+file.mimetype.substr(6)
    let newfilename=file.fieldname+Date.now()+ext
    cd(null,newfilename)
  }
})
var upload=multer({storage})
// var upload=multer({storage})
app.use(bodyparser.urlencoded({ extended:false}))
app.use(session({
  secret:"写点东西",
  resave:false,
  saveUninitialized:true,
  cookie:{
      maxAge:86400000
  }
}))


// view engine setup
app.set('views', path.join(__dirname, 'views'));
app.set('view engine', 'ejs');


app.use(logger('dev'));
app.use(express.json());
app.use(express.urlencoded({ extended: false }));
app.use(cookieParser());
app.use(express.static(path.join(__dirname, 'public')));
app.use(bodyparser.urlencoded({extended:false}))

app.use('/', indexRouter);
app.use('/users', usersRouter);
app.get("/api/ok",(req,res)=>{
  console.log(1)
  res.send("ok")
})
app.get('/api/captcha',(req,res)=>{
  var code=captcha.create({
    size:4,
    ignoreChars:"0oi12z9gl",
    noise:4,
    width:130,
    height:40,
    fontSize:50,
    charPreset:"1234567890"
  })
  res.type('svg')
  req.session.code=code.text
  res.send(code.data)
})
app.post("/api/cx",(req,res)=>{
  let sta={}
  var uname=req.body.uname
  console.log(uname)
  let sql=`SELECT * FROM user WHERE UID="${uname}"`
  db.query(sql,(erro,result)=>{
    if(erro!= null){
      console.log(erro)
      console.log("sqla语句错误")
      return false
    }
    if(result.length>0){
      sta.num=0
    }else{
      sta.num=1
    }
    res.send(sta)
  })
})

app.post("/api/gengxinspin",(req,res)=>{
  let rul=req.body.rul
  let ms=req.body.ms
  let kexuan=req.body.kexuan
  let id=req.body.id
  let sql=`UPDATE shangpin SET zongtag="${rul.type}",miaoshutag="${ms}",kexuan="${kexuan}",money="${rul.money}",stock="${rul.kc}" WHERE SID="${id}"`
  db.query(sql,(erro,result)=>{
    if(erro!=null){
      console.log("sql错误")
      res.send("cuole")
    }
    if(result.affectedRows>0){
      res.send("ok")
    }else{
      res.send(false)
    }
  })
})

app.post("/api/dianpuchadaohang",(req,res)=>{
  let id=req.body.id
  let sql=`SELECT navname FROM shangpu WHERE spID="${id}"`
  db.query(sql,(erro,result)=>{
    if(erro!=null){
      console.log("sql错误")
      res.send("cuole")
    }
    if(result.length>0){
      res.send(result)
    }
  })
})

app.post('/api/login',(req,res)=>{
  var sta={}
  var uname=req.body.uname
  var pwd=req.body.pwd
  console.log(req.body)
  var md5=crypto.createHash('md5')
  md5.update(pwd)
  var newpwd=md5.digest('hex')
  var sql=`SELECT * FROM user WHERE UID="${uname}" and zhuangtai="0"`
    db.query(sql,(erro,result)=>{
      if(erro!=null){
        console.log('sqlg语句错误')
        return false
      }
      if(result.length>0){
        if(result[0].pwd==newpwd){
          req.session.name=uname
          sta.num=1
        }else{
          sta.num=0
        }
      }else{
        sta.num=0
      }
      res.send(sta)
    })
  })
app.post("/api/zhuce",(req,res)=>{
  var sta={}
  var uname=req.body.uname
  var pwd=req.body.pwd
  var code=req.body.code
  var md5=crypto.createHash("md5")
  md5.update(pwd)
  var pwd=md5.digest("hex")
  if(code==req.session.code){
    let sql=`INSERT INTO user Values(null,"${uname}","${pwd}","新用户001",'/images/tx/mrtx.jpg',null,null,0,null,"1",null,null)`
    db.query(sql,(erro,result)=>{
      if(erro!= null){
        console.log("sql语句错误")
        return false
      }
      if(result.affectedRows>0){
        req.session.name=uname
        console.log(uname,req.session.name)
        sta.num=1
      }else{
        sta.num=0
      }
      res.send(sta)
    })
  }else{
    sta.num=2
    res.send(sta)
  }
})
app.get('/api/',(req,res)=>{
  var uname=req.session.name
  res.send(uname)
})

app.post("/api/key",(req,res)=>{
  let key=req.body.key
  let sql=`SELECT * FROM shangpin WHERE zongtag like "%${key}%" or miaoshutag like "%${key}%" or bixuan like "%${key}%"`
  db.query(sql,(erro,result)=>{
    if(erro!=null){
      console.log("sql语句错误")
      res.send("sqlcuole")
      return false
    }
    if(result.length>0){
      console.log(result)
      res.send(result)
    }else{
      res.send(false)
    }
  })
})

app.post("/api/xgspincha",(req,res)=>{
  let id=req.body.id
  let sql=`SELECT * FROM shangpin WHERE spid="${id}"`
  db.query(sql,(erro,result)=>{
    if(erro!=null){
      console.log("sql错误")
      res.send("cuole")
    }
    if(result.length>0){
      res.send(result)
    }else{
      res.send(false)
    }
  })
})

app.post("/api/xiugaidps",(req,res)=>{
  let rul=req.body.rul
  let spID=req.body.spID
  let img=req.body.img
  let sql=`UPDATE shangpu SET spname="${rul.name}",spimg="${img}",spinfo="${rul.desc}",dpleixin="${rul.region}" WHERE spID="${spID}"`
  db.query(sql,(erro,result)=>{
    if(erro!=null){
      console.log("sql错误")
      res.send("cuole")
    }
    if(result.affectedRows>0){
      res.send("ok")
    }else{
      res.send(false)
    }
  })
})

app.post("/api/htcha",(req,res)=>{
  let id=req.body.id
  let sql=`SELECT * FROM shangpu WHERE spID="${id}"`
  db.query(sql,(erro,result)=>{
    if(erro!=null){
      console.log("sql错误")
      res.send("cuole")
    }
    if(result.length>0){
      res.send(result)
    }else{
      res.send(false)
    }
  })
})

app.post("/api/shanchugouwu",(req,res)=>{
  let uname=req.session.name
  let sid=req.body.sid
  let spid=req.body.spid
  let sql=`DELETE FROM gouwuche WHERE uname="${uname}" and sid="${sid}" and spid="${spid}"`
  db.query(sql,(erro,result)=>{
    if(erro!=null){
      console.log("sql错误")
      res.send("cuole")
    }
    if(result.affectedRows>0){
      res.send("ok")
    }else{
      res.send(false)
    }
  })
})

app.post("/api/scspin",(req,res)=>{
  let sid=req.body.sid
  let uname=req.session.name
  let newsql=`SELECT * FROM user WHERE singlename like "%${sid}%"`
  db.query(newsql,(newerro,newresult)=>{
    if(newerro!=null){
      console.log("sql错误")
      res.send("cuole")
    }
    if(newresult.length>0){
      res.send("youle")
    }else{
      let sql=`SELECT a.shoucangzhe,b.singlename FROM shangpin as a,user as b WHERE a.SID="${sid}" and b.UID="${uname}"`
      db.query(sql,(erro,result)=>{
        if(erro!=null){
          console.log("sql错误")
          res.send("cuole")
        }
        if(result.length>0){
          if(result[0].shoucangzhe){
            var scwrap=result[0].shoucangzhe.split(',')
            scwrap.push(uname)
          }else{
            var scwrap=[]
            scwrap.push(uname)
          }
          if(result[0].singlename){
            var uwrap=result[0].singlename.split(',')
            uwrap.push(sid)
          }else{
            var uwrap=[]
            uwrap.push(sid)
          }
          let nsql=`UPDATE shangpin SET shoucangzhe="${scwrap}" WHERE SID="${sid}"`
          db.query(nsql,(nerro,nresult)=>{
            if(nerro!=null){
              console.log("sql语句错误")
              res.send("cuole")
              return false
            }
            if(nresult.affectedRows>0){
              let dsql=`UPDATE user SET singlename="${uwrap}" WHERE UID="${uname}"`
              db.query(dsql,(derro,dresult)=>{
                if(derro!=null){
                  console.log("sql语句错误")
                  res.send("cuole")
                  return false
                }
                if(dresult.affectedRows>0){
                  res.send("ok")
                }else{
                  res.send(false)
                }
              })
            }else{
              res.send(false)
            }
          })
        }else{
          res.send(false)
        }
      })
        }
  })
})

app.post("/api/mrdizhi",(req,res)=>{
  let dizhi=req.body.dizhi
  let uname=req.session.name
  let sql=`SELECT zongdizhi FROM user WHERE UID="${uname}"`
  db.query(sql,(erro,result)=>{
    if(erro!=null){
      console.log("sql错了")
      res.send("cuole")
    }
    if(result.length>0){
      var mrdz=JSON.stringify(dizhi)
      if(result[0].zongdizhi){
        var news=result[0].zongdizhi+"."+JSON.stringify(dizhi)
      }else{
        var news=JSON.stringify(dizhi)
        console.log("news"+news)
      }
      let sqls=`UPDATE user SET zongdizhi='${news}',moren='${mrdz}' WHERE UID="${uname}"`
      console.log(sqls)
      db.query(sqls,(erros,results)=>{
        if(erros!=null){
          console.log("sql错误",erros)
          res.send("cuole")
        }
        console.log(results)
        if(results.affectedRows>0){
          res.send("ok")
        }else{
          res.send(false)
        }
      })
    }else{
      res.send(false)
    }
  })
})

app.post('/api/pinglun',(req,res)=>{
  var GS=req.body.GS
  var SID=req.body.SID
  let sql1=`SELECT stock,sell FROM shangpin WHERE SID="${SID}"`
  db.query(sql1,(erro,result)=>{
    if(erro!=null){
      console.log("sql错误")
      res.send("cuole ")
    }
    if(result.length>0){
     var stock=result[0].stock-GS
     var sell=Number(result[0].sell)+GS
    
     let sql=`UPDATE shangpin SET stock='${stock}',sell='${sell}' WHERE SID="${SID}"`
    db.query(sql,(err,results)=>{
      if(err!=null){
        console.log("sql错误")
        res.send("cuole ")
      }
      if(results.affectedRows>0){
        res.send("ok")
      }else{
        res.send(false)
      }
    })
    }else{
      console.log('出错了')
    }
  })
})


app.post('/api/pinglun2',(req,res)=>{
  var pl=req.body.PL
  var cm=req.body.CM
  var ys=req.body.YS
  var SID=req.body.SID
  let sql=`SELECT comments FROM shangpin WHERE SID="${SID}"`
  db.query(sql,(erro,result)=>{
    if(erro!=null){
      console.log("sql错误")
      res.send("cuole ")
    }
    if(result.length>0){
      var uname=req.session.name
      if(result[0].comments==""){
        var carr=[]
      }else{
        var carr=result[0].comments.split(',')
      }
      carr.push(uname)
      let sql2=`UPDATE shangpin SET comments="${carr}" WHERE SID="${SID}"`
      db.query(sql2,(err,ress)=>{
        if(err!=null){
          console.log("sql错误")
          res.send("cuole ")
        }
        if(ress.affectedRows>0){
          var sql3=`INSERT INTO comments VALUES(null,'${SID}','${uname}','${pl}','${ys}','${cm}')`
          db.query(sql3,(err2,ress2)=>{
            if(err2!=null){
              console.log("sql错误")
              res.send("cuole ")
            }
            if(ress2.affectedRows>0){
              res.send(true)
            }
          })
        }
      })
    }
  })
})


app.post('/api/p',(req,res)=>{
  var sid=req.body.sid
  var sql=`SELECT * FROM comments WHERE SID="${sid}"`
  db.query(sql,(erro,result)=>{
    if(erro!=null){
      console.log("sql错误")
      res.send("cuole ")
    }
    if(result.length>0){
      res.send(result)
    }else{
      res.send(false)
    }
  })
})

app.post("/api/shimoren",(req,res)=>{
  let dizhi=JSON.stringify(req.body.dizhi)
  let key=req.body.key
  let mr=req.body.mr
  let uanem=req.session.name
  let sql=`SELECT zongdizhi,moren FROM user WHERE UID="${uname}"`
  db.query(sql,(erro,result)=>{
    if(erro!=null){
      console.log("sql错误")
      res.send("cuole ")
    }
    if(result.length>0){
      var zongwrap=result[0].zongdizhi.split('.')
      zongwrap.splice(key,1,dizhi)
      var arr=""
      for(var i=0;i<zongwrap.length;i++){
        if(i==0){
          arr=zongwrap[i]
        }else{
          arr=arr+'.'+zongwrap[i]
        }
      }
      if(mr){
        var sqls=`UPDATE user SET zongdizhi='${arr}',moren='${dizhi}' WHERE UID="${uname}"`
      }else{
        var sqls=`UPDATE user SET zongdizhi='${arr}',moren='' WHERE UID='${uname}'`
      }
      db.query(sqls,(erros,resuls)=>{
        if(erros!=null){
          console.log("sql错误")
          res.send("cuole")
        }
        if(results.affectedRows>0){
          res.send("ok")
        }else{
          res.send(false)
        }
      })
    }else{
      res.send(false)
    }
  })
})

app.post("/api/bushimoren",(req,res)=>{
  let dizhi=JSON.stringify(req.body.dizhi)
  let key=req.body.key
  let mr=req.body.mr
  let uname=req.session.name
  let sql=`SELECT zongdizhi FROM user WHERE UID="${uname}"`
  db.query(sql,(erro,result)=>{
    if(erro!=null){
      console.log("sql错误")
      res.send("cuole")
    }
    if(result.length>0){
      var zongwrap=result[0].zongdizhi.split('.')
      zongwrap.splice(key,1,dizhi)
      var arr=""
      for(var i=0;i<zongwrap.length;i++){
        if(i==0){
          arr=zongwrap[i]
        }else{
          arr=arr+'.'+zongwrap[i]
        }
      }
      if(mr){
        var sqls=`UPDATE user SET zongdizhi='${arr}',moren='${dizhi}' WHERE UID="${uname}"`
      }else{
        var sqls=`UPDATE user SET zongdizhi='${arr}' WHERE UID="${uname}"`
      }
      db.query(sqls,(erros,results)=>{
        if(erros!=null){
          console.log("sql错误")
          res.send("cuole")
        }
        if(results.affectedRows>0){
          res.send("ok")
        }else{
          res.send(false)
        }
      })
    }else{
      res.send(false)
    }
  })
})

app.post("/api/chagouwuche",(req,res)=>{
  let uname=req.session.name
  let sql=`SELECT * FROM gouwuche WHERE uname="${uname}"`
  db.query(sql,(erro,result)=>{
    if(erro!=null){
      console.log("sql错误")
      res.send("cuole")
    }
    if(result.length>0){
      res.send(result)
    }else{
      res.send(false)
    }
  })
})

app.post("/api/jiagouwuche",(req,res)=>{
  let gwc=req.body.gouwuche
  console.log(gwc)
  let uname=req.session.name
  let sqls=`SELECT * FROM gouwuche WHERE uname="${uname}"`
  db.query(sqls,(erros,results)=>{
    if(erros!=null){
      console.log("sql错误")
      res.send("cuole")
    }
    if(results.length>0){
      var panduan=false
      var sl=""
      var ccid=""
      for(var i=0;i<results.length;i++){
        if(results[i].sid==gwc.sid && results[i].spid==gwc.spid && results[i].yanse==gwc.yanse && results[i].chima==gwc.chima){
          panduan=true
          sl=results[i].shuliang
          ccid=results[i].Id
        }
      }
      if(panduan){
        var nsl=Number(sl)+Number(gwc.shuliang)
        let zsql=`UPDATE gouwuche SET shuliang="${nsl}" WHERE Id="${ccid}"`
        db.query(zsql,(zerro,zresult)=>{
          if(zerro!=null){
            console.log("zsql错误")
            res.send("cuole")
          }
          if(zresult.affectedRows>0){
            res.send("ok")
          }else{
            res.send(false)
          }
        })
        // console.log("cunzai")
        // res.send("cunzai")
      }else{
        let sql=`INSERT INTO gouwuche VALUES(null,'${uname}','${gwc.sid}','${gwc.spid}','${gwc.yimg}','${gwc.miaoshu}','${gwc.yanse}','${gwc.chima}','${gwc.danjia}','${gwc.shuliang}')`
        db.query(sql,(erro,result)=>{
          if(erro!=null){
            console.log('sql错误')
            res.send("cuole") 
          }
          if(result.affectedRows>0){
            res.send("ok")
          }else{
            res.send(false)
          }
        })
      }
    }else{
      let sql=`INSERT INTO gouwuche VALUES(null,'${uname}','${gwc.sid}','${gwc.spid}','${gwc.yimg}','${gwc.miaoshu}','${gwc.yanse}','${gwc.chima}','${gwc.danjia}')`
        db.query(sql,(erro,result)=>{
          if(erro!=null){
            console.log('sql错误')
            res.send("cuole") 
          }
          if(result.affectedRows>0){
            res.send("ok")
          }else{
            res.send(false)
          }
        })
    }
  })
})

app.post("/api/sheweimoren",(req,res)=>{
  let uname=req.session.name
  let newmr=req.body.newmr
  let sql=`UPDATE user SET moren='${newmr}' WHERE UID="${uname}"`
  db.query(sql,(erro,result)=>{
    if(erro!=null){
      console.log("sql错误")
      res.send("cuole ")
    }
    if(result.affectedRows>0){
      res.send("ok")
    }else{
      res.send(false)
    }
  })
})

app.post("/api/shanchudz",(req,res)=>{
  let key=req.body.key
  console.log(key)
  let uname=req.session.name
  let sql=`SELECT zongdizhi,moren FROM user WHERE UID="${uname}"`
  db.query(sql,(erro,result)=>{
    if(erro!=null){
      console.log("sql错误")
      res.send("cuole")
    }
    console.log(result)
    if(result.length>0){
      let dzwrap=result[0].zongdizhi.split('.')
      // if(dzwrap[key]==result[0].moren){
        console.log(123)
        let shan=dzwrap.splice(key,1)
        var gaidz=""
        console.log(dzwrap)
        if(dzwrap){
          for(var i=0;i<dzwrap.length;i++){
           if(i==0){
             gaidz=dzwrap[0]
           }else{
            gaidz=gaidz+'.'+dzwrap[i]
           }   
          }
        }
       if(shan==result[0].moren){
        var sqls=`UPDATE user SET zongdizhi='${gaidz}',moren="" WHERE UID="${uname}"`
       }else{
         var sqls=`UPDATE user SET zongdizhi='${gaidz}' WHERE UID="${uname}"`
       }
       db.query(sqls,(erros,results)=>{
         if(erros!=null){
           console.log("sqls错误")
           res.send("cuole")
         }
         if(results.affectedRows>0){
            res.send("ok")
         }else{
          res.send(false)
         }
       })
      // }
    }else{
      res.send(false)
    }
  })
})

app.post("/api/gdizhi",(req,res)=>{
  let dizhi=req.body.dizhi
  console.log(dizhi)
  let uname=req.session.name
  let sql=`SELECT zongdizhi FROM user WHERE UID="${uname}"`
  db.query(sql,(erro,result)=>{
    if(erro!=null){
      console.log("sql错了")
      res.send("cuole")
    }
    if(result.length>0){
      if(result[0].zongdizhi){
        var news=result[0].zongdizhi+"."+JSON.stringify(dizhi)
      }else{
        var news=JSON.stringify(dizhi)
        console.log("news"+news)
      }
      let sqls=`UPDATE user SET zongdizhi='${news}' WHERE UID="${uname}"`
      console.log(sqls)
      db.query(sqls,(erros,results)=>{
        if(erros!=null){
          console.log("sql错误",erros)
          res.send("cuole")
        }
        console.log(results)
        if(results.affectedRows>0){
          res.send("ok")
        }else{
          res.send(false)
        }
      })
    }else{
      res.send(false)
    }
  })
})

app.post("/api/dplists",(req,res)=>{
  let id=req.body.id
  let sql=`SELECT * FROM shangpin WHERE spid="${id}" limit 5`
  db.query(sql,(erro,result)=>{
    if(erro!=null){
      console.log("sql错误")
      res.send("cuole")
    }
    if(result.length>0){
      res.send(result)
    }else{
      res.send(false)
    }
  })
})

app.post("/api/chadizhi",(req,res)=>{
  let uname=req.session.name
  let sql=`SELECT zongdizhi,moren FROM user WHERE UID="${uname}"`
  db.query(sql,(erro,result)=>{
    if(erro!=null){
      console.log("sql错误")
      res.send("cuole")
    }
    if(result.length>0){
      res.send(result)
    }else{
      res.send(false)
    }
  })
})

app.post("/api/shanchudps",(req,res)=>{
  let spid=req.body.spid
  let uname=req.session.name
  let sql=`SELECT a.spinchoucang,b.spname FROM shangpu as a,user as b WHERE a.spID="${spid}" and b.UID="${uname}"`
  db.query(sql,(erro,result)=>{
    if(erro!=null){
      console.log("sql语句错误")
      res.send("cuole")
      return false
    }
    if(result.length>0){
      var key=""
      var awrap=result[0].spinchoucang.split(',')
      for(var i=0;i<awrap.length;i++){
        if(awrap[i]==uname){
          key=i
        }
      }
      awrap.splice(key,1)
      var num=""
      var bwrap=result[0].spname.split(',')
      for(var r=0;r<bwrap.length;r++){
        if(bwrap[i]==spid){
          num=i
        }
      }
      bwrap.splice(num,1)
      let ssql=`UPDATE shangpu SET spinchoucang="${awrap}" WHERE spID="${spid}"`
      db.query(ssql,(serro,sresult)=>{
        if(serro!=null){
          console.log("sql语句错误")
          res.send("cuole")
          return false
        }
        if(sresult.affectedRows>0){
          let bsql=`UPDATE user SET spname="${bwrap}" WHERE UID="${uname}"`
          db.query(bsql,(derro,dresult)=>{
            if(derro!=null){
              console.log("sql语句错误")
              res.send("cuole")
              return false
            }
            if(dresult.affectedRows>0){
              res.send(true)
            }else{
              res.send(false)
            }
          })
        }else{
          res.send(false)
        }
      })
    }else{
      res.send(false)
    }
  })
})

app.post("/api/chasc",(req,res)=>{
  let id=req.body.id
  let uname=req.session.name
  let sql=`SELECT spname FROM user WHERE UID="${uname}" and spname like "%${id}%"`
  db.query(sql,(erro,result)=>{
    if(erro!=null){
      console.log("sql错误")
      res.send("cuole")
      return false
    }
    if(result.length>0){
      res.send("ok")
      }else{
        res.send(false)
      }
  })
})

app.post("/api/chadps",(req,res)=>{
  let sid=req.body.sid
  let uname=req.session.name
  console.log("uname="+uname)
  let sql=`SELECT * FROM user WHERE UID="${uname}" and singlename like "%${sid}%"`
  db.query(sql,(erro,result)=>{
    if(erro!=null){
      console.log("sql错误")
      res.send("cuole")
      return false
    }
    console.log(result)
    if(result.length>0){
      res.send("ok")
      }else{
        res.send(false)
      }
  })
})

app.post("/api/bbsc",(req,res)=>{
  let uname=req.session.name
  let sql=`SELECT * FROM shangpin WHERE shoucangzhe like "%${uname}%"`
  db.query(sql,(erro,result)=>{
    if(erro!=null){
      console.log("sql错误")
      res.send("cuole")
    }
    if(result.length>0){
      res.send(result)
    }else{
      res.send(false)
    }
  })
})

app.post("/api/grindex",(req,res)=>{
  let uname=req.session.name
  let sql=`SELECT himg,uname,UID FROM user WHERE UID="${uname}"`
  db.query(sql,(erro,result)=>{
    if(erro!=null){
      console.log("sql错误")
      res.send("cuole")
    }
    console.log(result)
    if(result.length>0){
      res.send(result)
    }else{
      res.send(false)
    }
  })
})

app.post("/api/gerenxinxi",(req,res)=>{
  let uname=req.session.name
  let sql=`SELECT * FROM user WHERE UID="${uname}"`
  db.query(sql,(erro,result)=>{
    if(erro!=null){
      console.log("sql语句错误")
      res.send("cuole")
    }
    if(result.length>0){
      res.send(result)
    }else{
      res.send(false)
    }
  })
})

app.post("/api/shanchuscsp",(req,res)=>{
  let sid=req.body.sid
  let uname=req.session.name
  let sql=`SELECT a.shoucangzhe,b.singlename FROM shangpin as a,user as b WHERE a.SID="${sid}" and b.UID="${uname}"`
  db.query(sql,(erro,result)=>{
    if(erro!=null){
      console.log("sql语句错误")
      res.send("cuole")
    }
    if(result.length>0){
      let swrap=result[0].shoucangzhe.split(',')
      for(var i=0;i<swrap.length;i++){
        if(uname==swrap[i]){
          swrap.splice(i,1)
        }
      }
      let sqls=`UPDATE shangpin SET shoucangzhe="${swrap}" WHERE SID="${sid}"`
      db.query(sqls,(erros,results)=>{
        if(erros!=null){
          console.log("sql错误")
          res.send("cuole")
        }
        if(results.affectedRows>0){
          console.log(swrap,results)
          let uwrap=result[0].singlename.split(',')
          for(var j=0;j<uwrap.length;j++){
            if(sid==uwrap[j]){
              uwrap.splice(j,1)
            }
          }
          let sqln=`UPDATE user SET singlename="${uwrap}" WHERE UID="${uname}"`
          db.query(sqln,(erron,resultn)=>{
            if(erron!=null){
              console.log("sql错误")
              res.send("cuole")
            }
            if(resultn.affectedRows>0){
              console.log(uwrap,resultn)
              res.send("ok")
            }else{
              res.send(false)
            }
          })
        }else{
          res.send(false)
        }
      })
    }else{
      res.send(false)
    }
  })
})

app.post("/api/spdh",(req,res)=>{
  let id=req.body.id
  let sql=`SELECT navname,spname,dpleixin FROM shangpu WHERE spID="${id}"`
  db.query(sql,(erro,result)=>{
    if(erro!=null){
      console.log("sql语句错误")
      res.send("cuole")
      return false
    }
    if(result.length>0){
      console.log(result)
      res.send(result)
    }else{
      res.end(false)
    }
  })
})

app.post("/api/xggouwu",(req,res)=>{
  let id=req.body.id
  let rul=req.body.rul
  let sql=`UPDATE gouwuche SET yimg="${rul.yimg}",yanse="${rul.yanse}",chima="${rul.chima}" WHERE Id="${id}"`
  db.query(sql,(erro,result)=>{
    if(erro!=null){
      console.log("sql错误")
      res.send("cuole")
    }
    if(result.affectedRows>0){
      res.send("ok")
    }else{
      res.send(false)
    }
  })
})

app.post("/api/xxx",(req,res)=>{
  let id=req.body.id
  let sql=`SELECT * FROM shangpin WHERE SID="${id}"`
  db.query(sql,(erro,result)=>{
    if(erro!=null){
      console.log("sql错误")
      res.send("cuole")
    }
    if(result.length>0){
      res.send(result)
    }else{
      res.send(false)
    }
  })
})

app.post("/api/spsdh",(req,res)=>{
  let id=req.body.id
  // let sql=`SELECT a.navname,b.miaaoshutag,b.img,b.money,b.sell,b.comments FROM shangpu as a,shangpin as b WHERE a.spID="${id}" and b.spid="${id}"`
  let sql=`SELECT * FROM shangpin WHERE spid="${id}"`
  db.query(sql,(erro,result)=>{
    if(erro!=null){
      console.log("sql语句错误")
      res.send("cuole")
      return false
    }
    if(result.length>0){
      console.log(result)
      res.send(result)
    }else{
      res.send(false)
    }
  })
})

app.post("/api/spzyc",(req,res)=>{
  let id=req.body.id
  let sql=`SELECT navname,spinfo FROM shangpu WHERE spID="${id}"`
  db.query(sql,(erro,result)=>{
    if(erro!=null){
      console.log("sql语句错误")
      res.send("cuole")
      return false
    }
    if(result.length>0){
      res.send(result)
    }else{
      res.send(false)
    }
  })
})

app.post("/api/xianshifn",(req,res)=>{
  let spid=req.body.spid
  let navs=req.body.navs
  console.log(navs)
  let sql=`SELECT SID,miaoshutag FROM  shangpin WHERE spid="${spid}" and zongtag like "%${navs}%" LIMIT 5`
  db.query(sql,(erro,result)=>{
    if(erro!=null){
      console.log("sql语句")
      res.send("cuole")
      return false
    }
    if(result.length>0){
      res.send(result)
    }else{
      res.send(false)
    }
  })
})

app.post("/api/gxinxi",upload.single("file"),(req,res)=>{
  let sta={}
  let uname=req.session.name
  let nicheng=req.body.nicheng
  let zname=req.body.zname
  let sex=req.body.sex
  var img=`.${req.file.destination.substr(8)}/${req.file.filename}`
  let ext=req.file.mimetype.substr(6)
  let ntype={
    "jpg":1,
    "jpeg":1,
    "png":1,
  }
  if(ntype[ext]){
    let sql=`UPDATE user SET uname="${nicheng}",himg="${img}",zhenname="${zname}",sex="${sex}" WHERE UID="${uname}"`
    db.query(sql,(erro,result)=>{
      if(erro!=null){
        console.log("sql错误")
        res.send("cuole")
      }
      if(result.affectedRows>0){
        console.log(result)
        sta.num=0
      }else{
        sta.numm=1
      }
    })
  }else{
    sta.num=2
  }
  res.send(sta)
})

app.post("/api/sc",upload.single("file"),(req,res)=>{
  console.log(req.file.mimetype)
  var img=`.${req.file.destination.substr(8)}/${req.file.filename}`
  res.send(img)
})

app.post("/api/spin",upload.single('file'),(req,res)=>{
  console.log(req.file.mimetype)
  var img=`.${req.file.destination.substr(8)}/${req.file.filename}`
  res.send(img)
})

app.post("/api/tjspin",(req,res)=>{
  console.log(req.body)
  let rul=req.body.rul
  let img=req.body.img
  let tagms=req.body.tagms
  let tagbx=req.body.tagbx
  let danjia=req.body.danjia
  let num=req.body.num
  if(req.body.tagkx.length==0){
    console.log(1)
    var tagkx=null
  }else{
    console.log(2)
    var tagkx=req.body.tagkx
  }
  console.log(3)
  console.log(rul,img,tagms,tagbx,danjia,tagkx)
  let sql=`INSERT INTO shangpin values(null,"${rul.region}","${rul.name}","${rul.type}","${tagms}","${tagkx}","${tagbx}","${img}","${danjia}","${num}","0","0",null)`
  // let sql=`SELECT * FROM shangpin WHERE SID="${rul.region}"`
  db.query(sql,(erro,result)=>{
    if(erro!=null){
      console.log("sql语句错误")
      res.send("cuole")
      return false
    }
    console.log(22)
    let sta={}
    if(result.affectedRows>0){
      sta.num=1
    }else{
      sta.num=0
    }
    res.send(sta)
  })
})

app.post("/api/tm",(req,res)=>{
  var str=`SELECT img FROM shangpin LIMIT 8`
  db.query(str,(error,result)=>{
    console.log('res')
    if(error!=null){
      console.log("sql语句出错")
      res.send("/api")
      return false;
    }
    if(result.length>0){
      res.send(result)
    }else{
      res.send(false)
    }
})
})

app.post("/api/jiage",(req,res)=>{
  var str=`SELECT money FROM shangpin LIMIT 8`
  db.query(str,(error,result)=>{
    console.log('res')
    if(error!=null){
      console.log("sql语句出错")
      res.send("/api/")
      return false;
    }
    if(result.length>0){
      res.send(result)
    }else{
      res.send(false)
    }
})
})

app.post("/api/miaoshu",(req,res)=>{
  var str=`SELECT miaoshutag FROM shangpin LIMIT 8`
  db.query(str,(error,result)=>{
    console.log('res')
    if(error!=null){
      console.log("sql语句出错")
      res.send("/api/")
      return false;
    }
    if(result.length>0){
      res.send(result)
    }else{
      res.send(false)
    }
})
})

app.post("/api/zycdp",(req,res)=>{
  // let sql=`SELECT * FROM shangpu ORDER BY RAND() LIMIT 10;`
  let sql=`SELECT * FROM shangpu WHERE Id >= ((SELECT MAX(Id) FROM shangpu)-(SELECT MIN(Id) FROM shangpu)) * RAND() + (SELECT MIN(Id) FROM shangpu)  LIMIT 19`
  db.query(sql,(erro,result)=>{
    if(erro!=null){
      console.log("sql语句错误")
      res.send("cuole")
      return false
    }
    if(result.length>0){
      res.send(result)
    }else{
      res.send(false)
    }
  })

})

// app.post("/api/bbsc",(req,res)=>{
//   let uname=req.session.name
//   let sql=`SELECT *`
// })

app.post("/api/dpsc",(req,res)=>{
  let uname=req.session.name
  let sql=`SELECT * FROM  shangpu WHERE spinchoucang like "%${uname}%"`
  db.query(sql,(erro,result)=>{
    if(erro!=null){
      console.log("sql错误")
      res.send("cuole")
      return false
    }
    if(result.length>0){
      res.send(result)
    }else{
      res.send(false)
    }
  })
})

app.post("/api/jichucha",(req,res)=>{
  let jgw=req.body.jgw
  let id=req.body.id
  
  var sql=`SELECT * FROM shangpin WHERE spid="${id}"`
  db.query(sql,(erro,result)=>{
    if(erro!=null){
      console.log("sql语句错误")
      res.send("cuole")
      return false
    }
    if(result.length>0){
      var arr=[]
      if(jgw.mup && jgw.mdown){
        if(Number(jgw.mup)<Number(jgw.mdown)){
          var da=Number(jgw.mdown)
          var xiao=Number(jgw.mup)
          for(var i=0;i<result.length;i++){
            if(result[i].money>=xiao && result[i].money<=da){
              arr.push(result[i])
            }
          }
        }else if(Number(jgw.mup)==Number(jgw.mdown)){
          var bi=Number(jgw.mup)
          for(var i=0;i<result.length;i++){
            if(result[i].money>=bi){
              arr.push(result[i])
            }
          }
        }else{
          let da=Number(jgw.mup)
          let xiao=Number(jgw.mdown)
          for(var i=0;i<result.length;i++){
            if(result[i].money>=xiao && result[i].money<=da){
              arr.push(result[i])
            }
          }
        }
      }else if(jgw.mup || jgw.mdown){
        if(jgw.mup){
          var xiao=Number(jgw.mup)
        }else{
          var xiao=Number(jgw.mdown)
        }
        for(var i=0;i<result.length;i++){
          if(result[i].money>=xiao){
            arr.push(result[i])
          }
        }
      }else{
        arr=result
      }
      if(jgw.jichu){
        if(jgw.jichu=="销量"){
          function com(pro){
            return function(a,b){
              var value1=a[pro];
              var value2=b[pro];
              return value2 - value1;
            }
          }
          var narr=arr.sort(com('sell'))
        }else{
          function com(pro){
            return function(a,b){
              if(a[pro]){
                var value1=a[pro].split(',').length;
              }else{
                var value1=0
              }
              if(b[pro]){
                var value2=b[pro].split(',').length;
              }else{
                var value2=0
              }
              
              return value2 - value1;
            }
          }
          var narr=arr.sort(com('shoucangzhe'))
        }
      }else{
        if(jgw.up){
          function com(pro){
            return function(a,b){
              var value1=a[pro];
              var value2=b[pro];
              return value1 - value2;
            }
          }
          var narr=arr.sort(com('money'))
        }else{
          function com(pro){
            return function(a,b){
              var value1=a[pro];
              var value2=b[pro];
              return value2 - value1;
            }
          }
          var narr=arr.sort(com('money'))
        }
      }
      console.log(1)
      console.log(narr)
      res.send(narr)
    }else{
      res.send(false)
    }
  })
})

app.post("/api/gjcha",(req,res)=>{
  let data=req.body.gjdata
  let id=req.body.id
  if(Number(data.lowjg)<Number(data.upjg)){
    var da=data.upjg
    var xiao=data.lowjg
  }else{
    var da=data.lowjg
    var xiao=data.upjg
  }
  let sql=`SELECT * FROM shangpin WHERE miaoshutag like "%${data.gjzw}%" and spid="${id}"`
  db.query(sql,(erro,result)=>{
    if(erro!=null){
      console.log("sql语句错误")
      res.send("cuole")
      return false
    }
    if(result.length>0){
      let arrs=[]
      for(var i=0;i<result.length;i++){
        if(Number(result[i].money)>Number(xiao) && Number(result[i].money)<Number(da)){
          arrs.push(result[i])
        }
      }
      if(arrs.length!=0){
        res.send(arrs)
      }else{
        res.send(false)
      }
    }else{
      res.send(false)
    }
  })
})

app.post("/api/cebian",(req,res)=>{
  let id=req.body.id
  let sql=`SELECT * FROM shangpin WHERE spid="${id}"`
  db.query(sql,(erro,result)=>{
    if(erro!=null){
      console.log("sql错误")
      res.send("cuole")
    }
    if(result.length>0){
      res.send(result)
    }else{
      res.send(false)
    }
  })
})

app.post("/api/dhcha",(req,res)=>{
  let dnav=req.body.dnav
  let id=req.body.id
  let sql=`SELECT * FROM shangpin WHERE spid="${id}" and zongtag like "%${dnav}%"`
  db.query(sql,(erro,result)=>{
    if(erro!=null){
      console.log("sql语句错误")
      res.send("cuole")
      return false
    }
    if(result.length>0){
      res.send(result)
    }else{
      res.send(false)
    }
  })
})

app.post("/api/scdp",(req,res)=>{
  let id=req.body.id
  let scname=req.session.name
  let sql=`SELECT a.spinchoucang,b.spname FROM shangpu as a,user as b WHERE a.spID="${id}" and b.UID="${scname}"`
  db.query(sql,(erro,result)=>{
    if(erro!=null){
      console.log("sql语句错误")
      res.send("cuole")
      return false
    }
    if(result.length>0){
      console.log(result)
     if(result[0].spinchoucang){
      var scwrap=result[0].spinchoucang.split(',')
      scwrap.push(scname)
     }else{
        var scwrap=[]
        scwrap.push(scname)
     }
      let nsql=`UPDATE shangpu SET spinchoucang="${scwrap}" WHERE spID="${id}"`
      db.query(nsql,(erros,results)=>{
        if(erros!=null){
          console.log("sql语句错误")
          res.send("cuole")
          return false
        }
        if(results.affectedRows>0){
          console.log(results)
          if(result[0].spname){
            var snwrap=result[0].spname.split(',')
            snwrap.push(id)
          }else{
            var snwrap=[]
            snwrap.push(id)
          }
          let dsql=`UPDATE user SET spname="${snwrap}" WHERE UID="${scname}"`
          db.query(dsql,(errod,resultd)=>{
            if(errod!=null){
              console.log("sql语句错误")
              res.send("cuole")
              return false
            }
            if(resultd.affectedRows>0){
              console.log(resultd)
              res.send("ok")
            }
          })
        }else{
          res.send(false)
        }
      })
    }else{
      res.send(false)
    }
  })
})

app.post("/api/shanchudp",(req,res)=>{
  let id=req.body.id
  let scname=req.session.name
  console.log(id,scname)
  let sql=`SELECT a.spinchoucang,b.spname FROM shangpu as a,user as b WHERE a.spID="${id}" and b.UID="${scname}"`
  db.query(sql,(erro,result)=>{
    if(erro!=null){
      console.log("sql语句错误")
      res.send("cuole")
      return false
    }
    console.log(result)
    if(result.length>0){
      let spn=result[0].spname.split(',')
      for(var i=0;i<spn.length;i++){
        if(id==spn[i]){
          spn.splice(i,1)
        }
      }
      let sqls=`UPDATE user SET spname="${spn}" WHERE UID="${scname}"`
      db.query(sqls,(erros,results)=>{
        if(erros!=null){
          console.log("sql语句错误")
          res.send("cuole")
          return false
        }
        if(results.affectedRows>0){
          let spsc=result[0].spinchoucang.split(',')
          for(var i=0;i<spsc.length;i++){
            if(scname==spsc[i]){
              spsc.splice(i,1)
            }
          }
          let sqln=`UPDATE shangpu SET spinchoucang="${spsc}" WHERE spID="${id}"`
          db.query(sqln,(erron,resultn)=>{
            if(erron!=null){
              console.log("sql语句错误")
              res.send("cuole")
              return false
            }
            if(resultn.affectedRows>0){
              res.send("ok")
            }else{
              res.send(false)
            }
          })
        }else{
          res.send(false)
        }
      })
    }else{
      res.send(false)
    }
  })
})

app.post("/api/dptj",(req,res)=>{
  let tag=req.body.tag
  let rul=req.body.rul
  let img=req.body.img
  let sql=`INSERT INTO shangpu Values(null,"${rul.id}","${rul.name}","${tag}","${img}","${rul.region}","${rul.desc}",0,null)`
  db.query(sql,(erro,result)=>{
    if(erro!=null){
      console.log("sql语句错误")
      res.send("cuole")
      return false
    }
    let sta={}
    if(result.affectedRows>0){
      sta.num=1
    }else{
      sta.num=0
    }
    res.send(sta)
  }) 
})

app.post('/api/dpid',(req,res)=>{
  var sta={}
  var newid=req.body.dpid
  let sql=`SELECT spname FROM shangpu WHERE spID="${newid}"`
  db.query(sql,(erro,result)=>{
    if(erro!=null){
      console.log("sql语句错误")
      res.send("sql语句错误")
      return false
    }
    if(result.length>0){
      sta.num=0
    }else{
      sta.num=1
    }
    res.send(sta)
  })
})

app.post('/api/dpids',(req,res)=>{
  var newid=req.body.dpid
  let sql=`SELECT navname FROM shangpu WHERE spID="${newid}"`
  db.query(sql,(erro,result)=>{
    if(erro!=null){
      console.log("sql语句错误")
      res.send("sql语句错误")
      return false
    }
    if(result.length>0){
      res.send(result)
    }else{
      res.send(false)
    }
  })
})

app.post("/api/danpins",(req,res)=>{
  let id=req.body.id
  let sid=req.body.sid
   // let sql=`SELECT a.navname,b.miaaoshutag,b.img,b.money,b.sell,b.comments FROM shangpu as a,shangpin as b WHERE a.spID="${id}" and b.spid="${id}"`
  let sql=`SELECT a.SID,a.spid,a.miaoshutag,a.kexuan,a.bixuan,a.img,a.money,a.stock,a.sell,a.shoucangzhe,a.comments,b.spname FROM shangpin as a,shangpu as b WHERE a.sid="${sid}" and b.spID="${id}"`
  db.query(sql,(erro,result)=>{
    if(erro!=null){
      console.log("sql语句错误",erro)
      res.send("cuole")
      return false
    }
    if(result.length>0){
      res.send(result)
    }else{
      res.send(false)
    }
  })
})

app.post('/api/spinid',(req,res)=>{
  let spinid=req.body.spinid
  let sql=`SELECT Id FROM shangpin WHERE SID="${spinid}"`
  db.query(sql,(erro,result)=>{
    if(erro!=null){
      console.log("sql错了")
      res.send("cuole")
      return false
    }
    let sta={}
    if(result.length>0){
      sta.num=0
    }else{
      sta.num=1
    }
    res.send(sta)
  })
})

app.get('/api/tc',(req,res)=>{
  req.session.name=false
  console.log(req.session.name)
  var sta={}
  sta.num=1
  res.send(sta)
})

app.post("/api/schuspins",(req,res)=>{
  let id=req.body.id
  let spid=req.body.spid
  let sql=`DELETE FROM shangpin WHERE SID="${id}"`
  db.query(sql,(erro,result)=>{
    if(erro!=null){
      console.log("sql错误")
      res.send("cuole")
    }
    if(result.affectedRows>0){
      let sqls=`SELECT * FROM shangpin WHERE spid="${spid}"`
      db.query(sqls,(erros,results)=>{
        if(erros!=null){
          console.log("sqll错误")
          res.send("cuole")
        }
        if(results.length>0){
          res.send(results)
        }else{
          res.send(false)
        }
      })
    }else{
      res.send(false)
    }
  })
})

app.post("/api/shanchuyonghu",(req,res)=>{
  let id=req.body.id
  let sql=`DELETE FROM user WHERE UID="${id}"`
  db.query(sql,(erro,result)=>{
    if(erro!=null){
      console.log("sql错误")
      res.send("cuole")
    }
    if(result.affectedRows>0){
      let sqls=`SELECT * FROM user`
      db.query(sqls,(erros,results)=>{
        if(erros!=null){
          console.log("sql错误")
          res.send("cuole")
        }
        if(results.length>0){
          res.send(results)
        }else{
          res.send(false)
        }
      })
    }else{
      res.send(false)
    }
  })
})

app.post("/api/chachacha",(req,res)=>{
  let sql=`SELECT * FROM shangpin WHERE Id >= ((SELECT MAX(Id) FROM shangpin)-(SELECT MIN(Id) FROM shangpin)) * RAND() + (SELECT MIN(Id) FROM shangpin)  LIMIT 9`
  db.query(sql,(erro,result)=>{
    if(erro!=null){
      console.log("sql错误")
      res.send("cuole")
    }
    if(result.length>0){
      res.send(result)
    }else{
      res.send(false)
    }
  })
})

app.post("/api/xiugaiyonghu",(req,res)=>{
  let rul=req.body.rul
  let id=req.body.id
  let sql=`UPDATE user SET uname="${rul.name}",himg="${rul.img}",sex="${rul.region}",zhuangtai="${rul.resource}" WHERE UID="${id}"`
  db.query(sql,(erro,result)=>{
    if(erro!=null){
      console.log("sql错误")
      res.send("cuole")
    }
    if(result.affectedRows>0){
      res.send("ok")
    }else{
      res.send(false)
    }
  })
})

app.get("/api/dpyilan",(req,res)=>{
  let sql=`SELECT * FROM user`
  db.query(sql,(erro,result)=>{
    if(erro!=null){
      console.log("sql错误")
      res.send("cuole")
    }
    if(result.length>0){
      res.send(result)
    }else{
      res.send(false)
    }
  })
})

app.post('/api/htlogin',(req,res)=>{
  var sta={}
  var uname=req.body.uname
  var pwd=req.body.pwd
  var newpwd=req.body.newpwd
  var md5=crypto.createHash('md5')
  md5.update(pwd)
  var newpwd=md5.digest('hex')
  var sql=`SELECT * FROM houtaidl WHERE uname="${uname}"`
    db.query(sql,(erro,result)=>{
      if(erro!=null){
        console.log('sqlg语句错误')
        return false
      }
      if(result.length>0){
        if(result[0].pwd==newpwd){
          req.session.aname=uname
          sta.num=1
        }else{
          sta.num=0
        }
      }else{
        sta.num=0
      }
      res.send(sta)
    })
  })

 app.get("/api/hduser",(req,res)=>{
   let aname=req.session.aname
   res.send(aname)
 })
  
app.get("/api/sp",(req,res)=>{
	var str=`SELECT * FROM shangpu `
  var uname=req.session.name
	db.query(str,(error,result)=>{
		if(error!=null){
			console.log("sql语句出错")
			res.send("tianmao")
			return false;
		}
    if(result.length>0){
      res.render("/api/sp",{result,uname})
    }
  })
})

// catch 404 and forward to error handler
app.use(function(req, res, next) {
  next(createError(404));
});

// error handler
app.use(function(err, req, res, next) {
  // set locals, only providing error in development
  res.locals.message = err.message;
  res.locals.error = req.app.get('env') === 'development' ? err : {};

  // render the error page
  res.status(err.status || 500);
  res.render('error');
  
  // app.get("/api/tc",(req,res)=>{
  //   req.session.uname=false
  //   var sta={}
  //    sta.num=1
  //   res.send(sta)
  // })
  // app.post("/chushimima",(req,res)=>{
  //   var pwd=req.body.pwd
  //   if(pwd){
  //     res.send(true)
  //   }else{
  //     res.send(false)
  //   }
  // })
});

module.exports = app;
